﻿/********************************************************************************************************************
* Author:	Christos Polydorou																						*
* Email:	std04237@di.uoa.gr																						*
* Purpose:	This script is used in order to create the stored procedure "usp_userLogin"				 		*
*			that returns the userid, the email and the lastlogin time of a user givven the username and the			*
*			password. If the user is not enabled it returns -1, if the password is wrong -2 and if the user is		*
*			locked -3.																								*
********************************************************************************************************************/

CREATE PROCEDURE [dbo].[usp_userLogin]
	@username nvarchar(50), @password nvarchar(50)
AS
	DECLARE @id bigint
	DECLARE @enabled int
	DECLARE @loginattempts int
	DECLARE @timelocked datetime
	DECLARE @lastlogin datetime
	DECLARE @userpassword nvarchar(50)
	DECLARE @email nvarchar(50)
	DECLARE @maxloginattempts int
	DECLARE @minstolock int
	DECLARE @usertype nvarchar(3)
	SET @minstolock = 5
	SET @maxloginattempts = 4

	SELECT @id = Users.ID, @userpassword = Users.Password, @loginattempts = Users.LoginAttempts, @usertype = Users.Usertype,
		   @timelocked = Users.TimeLocked, @enabled = IsEnabled, @lastlogin = Users.LastLogin, @email = Users.Email
	FROM Users
	WHERE [Users].Username = @username

	IF @enabled = 0												/* user is not enabled */
		return -1

	IF @timelocked > DATEADD(MINUTE,-@minstolock,GETDATE())		/* user is not disabled, the password is correct */
		RETURN -3

	IF @userpassword != @password								/* wrong password */
		BEGIN
			IF @loginattempts < @maxloginattempts				/* do not lock user if attempts are less than 5 */
				BEGIN
					SET @loginattempts = @loginattempts + 1
					UPDATE Users
					SET users.LoginAttempts = @loginattempts
					WHERE Users.ID = @id
					SET @loginattempts = @loginattempts - 1		/* adjust the @loginattempts value for the following if statement */
				END
			IF @loginattempts = @maxloginattempts							
				BEGIN
					SET @loginattempts = @loginattempts + 1
					UPDATE Users								/* lock user at the next attempt */
					SET users.TimeLocked = CURRENT_TIMESTAMP, users.LoginAttempts = @loginattempts
					WHERE Users.ID = @id
					SET @loginattempts = @loginattempts - 1		/* adjust the @loginattempts value for the following if statement */
				END
			IF @loginattempts > @maxloginattempts
				BEGIN
					UPDATE Users								/* lock user at the next attempt */
					SET users.LoginAttempts = 1
					WHERE Users.ID = @id
				END
			RETURN -2
		END

	UPDATE Users												/* user is enabled, not locked and the password is correct */
	SET users.LastLogin = CURRENT_TIMESTAMP, users.LoginAttempts = 0
	WHERE Users.ID = @id
	
	SET NOCOUNT OFF
	SELECT @id, @email, @usertype, @lastlogin	
	RETURN @id
GO